--#SET TERMINATOR @

-- Drop table 'ALLDATATYPES'
BEGIN
  IF EXISTS (SELECT * FROM SYSCAT.TABLES
             WHERE TABSCHEMA = CURRENT_SCHEMA
             AND TABNAME = 'ALLDATATYPES') THEN
    EXECUTE IMMEDIATE 'DROP TABLE ALLDATATYPES';
  END IF;
END @

-- Drop table 'DEPARTMENTS'
BEGIN
  IF EXISTS (SELECT * FROM SYSCAT.TABLES
             WHERE TABSCHEMA = CURRENT_SCHEMA
             AND TABNAME = 'DEPARTMENTS') THEN
    EXECUTE IMMEDIATE 'DROP TABLE DEPARTMENTS';
  END IF;
END @

-- Drop table 'EMPLOYEES'
BEGIN
  IF EXISTS (SELECT * FROM SYSCAT.TABLES
             WHERE TABSCHEMA = CURRENT_SCHEMA
             AND TABNAME = 'EMPLOYEES') THEN
    EXECUTE IMMEDIATE 'DROP TABLE EMPLOYEES';
  END IF;
END @

-- Drop table 'ITEMBRANCHES'
BEGIN
  IF EXISTS (SELECT * FROM SYSCAT.TABLES
             WHERE TABSCHEMA = CURRENT_SCHEMA
             AND TABNAME = 'ITEMBRANCHES') THEN
    EXECUTE IMMEDIATE 'DROP TABLE ITEMBRANCHES';
  END IF;
END @

-- Drop table 'ITEMS'
BEGIN
  IF EXISTS (SELECT * FROM SYSCAT.TABLES
             WHERE TABSCHEMA = CURRENT_SCHEMA
             AND TABNAME = 'ITEMS') THEN
    EXECUTE IMMEDIATE 'DROP TABLE ITEMS';
  END IF;
END @

-- Drop table 'TESTFOREIGNKEYOPTIONS'
BEGIN
  IF EXISTS (SELECT * FROM SYSCAT.TABLES
             WHERE TABSCHEMA = CURRENT_SCHEMA
             AND TABNAME = 'TESTFOREIGNKEYOPTIONS') THEN
    EXECUTE IMMEDIATE 'DROP TABLE TESTFOREIGNKEYOPTIONS';
  END IF;
END @

-- Drop table 'TESTFOREIGNKEYOPTIONS2'
BEGIN
  IF EXISTS (SELECT * FROM SYSCAT.TABLES
             WHERE TABSCHEMA = CURRENT_SCHEMA
             AND TABNAME = 'TESTFOREIGNKEYOPTIONS2') THEN
    EXECUTE IMMEDIATE 'DROP TABLE TESTFOREIGNKEYOPTIONS2';
  END IF;
END @

-- Drop table 'TESTFOREIGNKEYOPTIONS3'
BEGIN
  IF EXISTS (SELECT * FROM SYSCAT.TABLES
             WHERE TABSCHEMA = CURRENT_SCHEMA
             AND TABNAME = 'TESTFOREIGNKEYOPTIONS3') THEN
    EXECUTE IMMEDIATE 'DROP TABLE TESTFOREIGNKEYOPTIONS3';
  END IF;
END @

-- Drop table 'TESTFOREIGNKEYOPTIONS4'
BEGIN
  IF EXISTS (SELECT * FROM SYSCAT.TABLES
             WHERE TABSCHEMA = CURRENT_SCHEMA
             AND TABNAME = 'TESTFOREIGNKEYOPTIONS4') THEN
    EXECUTE IMMEDIATE 'DROP TABLE TESTFOREIGNKEYOPTIONS4';
  END IF;
END @

-- Drop table 'ZIPCODES'
BEGIN
  IF EXISTS (SELECT * FROM SYSCAT.TABLES
             WHERE TABSCHEMA = CURRENT_SCHEMA
             AND TABNAME = 'ZIPCODES') THEN
    EXECUTE IMMEDIATE 'DROP TABLE ZIPCODES';
  END IF;
END @

-- Create table 'ALLDATATYPES'
CREATE TABLE ALLDATATYPES (
   DATATYPEID integer GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL
  ,DATATYPENAME varchar(15) NOT NULL
  ,DTSMALLINT smallint NOT NULL DEFAULT 1
  ,DTINT integer NOT NULL DEFAULT 2
  ,DTBIGINT bigint NOT NULL DEFAULT 3
  ,DTDECIMAL decimal(31, 0) NOT NULL DEFAULT 4
  ,DTDECIMAL_8_2 decimal(8, 2) NOT NULL DEFAULT 5
  ,DTREAL real NOT NULL DEFAULT 6
  ,DTDOUBLE double NOT NULL DEFAULT 7
  ,DTDECFLOAT decfloat NOT NULL
  ,DTCHAR character(200) NOT NULL
  ,DTVARCHAR varchar(255 CODEUNITS32) NOT NULL
  ,DTNCHAR graphic(100) NOT NULL DEFAULT ''
  ,DTNVARCHAR vargraphic(255) NOT NULL
  ,DTGRAPHIC graphic(100) NOT NULL
  ,DTVARGRAPHIC vargraphic(255) NULL
  ,DTCLOB clob NULL
  ,DTCLOB_2000 clob(2000) NULL
  ,DTDBCLOB dbclob NULL
  ,DTDBCLOB_4K dbclob(4K) NULL
  ,DTDATE date NULL
  ,DTTIME time NOT NULL
  ,DTTIMESTAMP timestamp NOT NULL
  ,DTXML xml NULL
  ,CONSTRAINT PK_ALLDATATYPES PRIMARY KEY (DATATYPEID)
) @
CREATE UNIQUE INDEX IK_ALLDATATYPES_DATATYPENAME ON ALLDATATYPES (DATATYPENAME) CLUSTER @

COMMENT ON TABLE ALLDATATYPES IS 'Sample table for most common data types'' definitions.' @
COMMENT ON COLUMN ALLDATATYPES.DATATYPEID IS 'Test single'' quatation' @
COMMENT ON COLUMN ALLDATATYPES.DATATYPENAME IS 'Test single quatation''s name' @
COMMENT ON COLUMN ALLDATATYPES.DTSMALLINT IS 'Numeric Data Types' @
COMMENT ON COLUMN ALLDATATYPES.DTINT IS 'Numeric Data Types' @
COMMENT ON COLUMN ALLDATATYPES.DTBIGINT IS 'Numeric Data Types' @
COMMENT ON COLUMN ALLDATATYPES.DTDECIMAL IS 'Numeric Data Types' @
COMMENT ON COLUMN ALLDATATYPES.DTDECIMAL_8_2 IS 'Numeric Data Types' @
COMMENT ON COLUMN ALLDATATYPES.DTREAL IS 'Floating point number' @
COMMENT ON COLUMN ALLDATATYPES.DTDOUBLE IS 'Floating point number' @
COMMENT ON COLUMN ALLDATATYPES.DTDECFLOAT IS 'Floating point number' @
COMMENT ON COLUMN ALLDATATYPES.DTCHAR IS 'Character Data Types' @
COMMENT ON COLUMN ALLDATATYPES.DTVARCHAR IS 'Character Data Types' @
COMMENT ON COLUMN ALLDATATYPES.DTNCHAR IS 'Character Data Types' @
COMMENT ON COLUMN ALLDATATYPES.DTNVARCHAR IS 'Character Data Types' @
COMMENT ON COLUMN ALLDATATYPES.DTGRAPHIC IS 'Character Data Types' @
COMMENT ON COLUMN ALLDATATYPES.DTVARGRAPHIC IS 'Character Data Types' @
COMMENT ON COLUMN ALLDATATYPES.DTCLOB IS 'Large Object Data Types' @
COMMENT ON COLUMN ALLDATATYPES.DTCLOB_2000 IS 'Large Object Data Types' @
COMMENT ON COLUMN ALLDATATYPES.DTDBCLOB IS 'Large Object Data Types' @
COMMENT ON COLUMN ALLDATATYPES.DTDBCLOB_4K IS 'Large Object Data Types' @
COMMENT ON COLUMN ALLDATATYPES.DTDATE IS 'Date and Time' @
COMMENT ON COLUMN ALLDATATYPES.DTTIME IS 'Date and Time' @
COMMENT ON COLUMN ALLDATATYPES.DTTIMESTAMP IS 'Date and Time' @
COMMENT ON COLUMN ALLDATATYPES.DTXML IS 'XML' @

-- Create table 'DEPARTMENTS'
CREATE TABLE DEPARTMENTS (
   DEPARTMENTID integer GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL
  ,DEPARTMENTNAME vargraphic(50) NOT NULL
  ,PARENTID integer NULL
  ,MANAGERID integer NULL
  ,CONSTRAINT PK_DEPARTMENTS PRIMARY KEY (DEPARTMENTID)
) @

COMMENT ON TABLE DEPARTMENTS IS 'The department table.' @
COMMENT ON COLUMN DEPARTMENTS.DEPARTMENTID IS 'Department ID' @
COMMENT ON COLUMN DEPARTMENTS.DEPARTMENTNAME IS 'Department Name' @
COMMENT ON COLUMN DEPARTMENTS.PARENTID IS 'Parent Department' @
COMMENT ON COLUMN DEPARTMENTS.MANAGERID IS 'Manager' @

-- Create table 'EMPLOYEES'
CREATE TABLE EMPLOYEES (
   EMPLOYEEID integer GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL
  ,LASTNAME vargraphic(50) NOT NULL
  ,FIRSTNAME vargraphic(50) NOT NULL
  ,DEPARTMENTID integer NOT NULL
  ,CONSTRAINT PK_EMPLOYEES PRIMARY KEY (EMPLOYEEID)
) @
CREATE INDEX IK_EMPLOYEES_FIRSTNAME_LASTNAME ON EMPLOYEES (FIRSTNAME, LASTNAME) @
CREATE INDEX IK_EMPLOYEES_LASTNAME ON EMPLOYEES (LASTNAME) @

COMMENT ON TABLE EMPLOYEES IS 'Employees' @
COMMENT ON COLUMN EMPLOYEES.EMPLOYEEID IS 'EmployeeID' @
COMMENT ON COLUMN EMPLOYEES.LASTNAME IS 'Last Name' @
COMMENT ON COLUMN EMPLOYEES.FIRSTNAME IS 'First Name' @
COMMENT ON COLUMN EMPLOYEES.DEPARTMENTID IS 'Department' @

-- Create table 'ITEMBRANCHES'
CREATE TABLE ITEMBRANCHES (
   ITEMID integer NOT NULL
  ,SUBITEMID integer NOT NULL
  ,BRANCHID integer NOT NULL
  ,ITEMVALUE vargraphic(255) NOT NULL
  ,CONSTRAINT PK_ITEMBRANCHES PRIMARY KEY (ITEMID, SUBITEMID, BRANCHID)
) @
CREATE INDEX IK_ITEMBRANCHES_ITEMID_SUBITEMID ON ITEMBRANCHES (ITEMID, SUBITEMID) @
CREATE INDEX IK_ITEMBRANCHES_SUBITEMID_ITEMID ON ITEMBRANCHES (SUBITEMID, ITEMID) @

-- Create table 'ITEMS'
CREATE TABLE ITEMS (
   ITEMID integer NOT NULL
  ,SUBITEMID integer NOT NULL
  ,ITEMNAME vargraphic(255) NOT NULL
  ,CONSTRAINT PK_ITEMS PRIMARY KEY (ITEMID, SUBITEMID)
) @

-- Create table 'TESTFOREIGNKEYOPTIONS'
CREATE TABLE TESTFOREIGNKEYOPTIONS (
   DEPARTMENTID integer NOT NULL
  ,MEMO vargraphic(50) NOT NULL
  ,CONSTRAINT PK_TESTFOREIGNKEYOPTIONS PRIMARY KEY (DEPARTMENTID)
) @

COMMENT ON TABLE TESTFOREIGNKEYOPTIONS IS 'Test ForeignKey Actions: CASCADE/NULL/RESTRICT/No Action' @

-- Create table 'TESTFOREIGNKEYOPTIONS2'
CREATE TABLE TESTFOREIGNKEYOPTIONS2 (
   OPTIONID integer GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL
  ,DEPARTMENTID integer NULL
  ,MEMO vargraphic(50) NOT NULL
  ,CONSTRAINT PK_TESTFOREIGNKEYOPTIONS2 PRIMARY KEY (OPTIONID)
) @

COMMENT ON TABLE TESTFOREIGNKEYOPTIONS2 IS 'Test ForeignKey Actions: CASCADE/NULL/RESTRICT/No Action' @

-- Create table 'TESTFOREIGNKEYOPTIONS3'
CREATE TABLE TESTFOREIGNKEYOPTIONS3 (
   OPTIONID integer GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL
  ,DEPARTMENTID integer NULL
  ,MEMO vargraphic(50) NOT NULL
  ,CONSTRAINT PK_TESTFOREIGNKEYOPTIONS3 PRIMARY KEY (OPTIONID)
) @

COMMENT ON TABLE TESTFOREIGNKEYOPTIONS3 IS 'Test ForeignKey Actions: CASCADE/NULL/RESTRICT/No Action' @

-- Create table 'TESTFOREIGNKEYOPTIONS4'
CREATE TABLE TESTFOREIGNKEYOPTIONS4 (
   OPTIONID integer GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL
  ,DEPARTMENTID integer NULL
  ,MEMO vargraphic(50) NOT NULL
  ,CONSTRAINT PK_TESTFOREIGNKEYOPTIONS4 PRIMARY KEY (OPTIONID)
) @

COMMENT ON TABLE TESTFOREIGNKEYOPTIONS4 IS 'Test ForeignKey Actions: CASCADE/NULL/RESTRICT/No Action' @

-- Create table 'ZIPCODES'
CREATE TABLE ZIPCODES (
   ZIPCODE varchar(8) NOT NULL
  ,ADDRESS1 vargraphic(255) NOT NULL
  ,ADDRESS2 vargraphic(255) NOT NULL DEFAULT ''
  ,ADDRESS3 vargraphic(255) NOT NULL DEFAULT ''
) @
CREATE INDEX IK_ZIPCODES_ZIPCODE ON ZIPCODES (ZIPCODE) CLUSTER @

COMMENT ON TABLE ZIPCODES IS 'Zip codes' @
COMMENT ON COLUMN ZIPCODES.ZIPCODE IS 'Zip code is not unique.' @
COMMENT ON COLUMN ZIPCODES.ADDRESS1 IS 'Address1' @
COMMENT ON COLUMN ZIPCODES.ADDRESS2 IS 'Address2' @
COMMENT ON COLUMN ZIPCODES.ADDRESS3 IS 'Address3' @


-- Foreign keys for table 'DEPARTMENTS'
ALTER TABLE DEPARTMENTS
  ADD CONSTRAINT FK_DEPARTMENTS_MANAGERID
  FOREIGN KEY (MANAGERID)
  REFERENCES EMPLOYEES(EMPLOYEEID) @
ALTER TABLE DEPARTMENTS
  ADD CONSTRAINT FK_DEPARTMENTS_PARENTID
  FOREIGN KEY (PARENTID)
  REFERENCES DEPARTMENTS(DEPARTMENTID) @

-- Foreign keys for table 'EMPLOYEES'
ALTER TABLE EMPLOYEES
  ADD CONSTRAINT FK_EMPLOYEES_DEPARTMENTID
  FOREIGN KEY (DEPARTMENTID)
  REFERENCES DEPARTMENTS(DEPARTMENTID) @

-- Foreign keys for table 'ITEMBRANCHES'
ALTER TABLE ITEMBRANCHES
  ADD CONSTRAINT FK_ITEMBRANCHES_ITEMID_SUBITEMID
  FOREIGN KEY (ITEMID,SUBITEMID)
  REFERENCES ITEMS(ITEMID,SUBITEMID) ON DELETE CASCADE @

-- Foreign keys for table 'TESTFOREIGNKEYOPTIONS'
ALTER TABLE TESTFOREIGNKEYOPTIONS
  ADD CONSTRAINT FK_TESTFOREIGNKEYOPTIONS_DEPARTMENTID
  FOREIGN KEY (DEPARTMENTID)
  REFERENCES DEPARTMENTS(DEPARTMENTID) @

-- Foreign keys for table 'TESTFOREIGNKEYOPTIONS2'
ALTER TABLE TESTFOREIGNKEYOPTIONS2
  ADD CONSTRAINT FK_TESTFOREIGNKEYOPTIONS2_DEPARTMENTID
  FOREIGN KEY (DEPARTMENTID)
  REFERENCES DEPARTMENTS(DEPARTMENTID) ON DELETE CASCADE ON UPDATE RESTRICT @

-- Foreign keys for table 'TESTFOREIGNKEYOPTIONS3'
ALTER TABLE TESTFOREIGNKEYOPTIONS3
  ADD CONSTRAINT FK_TESTFOREIGNKEYOPTIONS3_DEPARTMENTID
  FOREIGN KEY (DEPARTMENTID)
  REFERENCES DEPARTMENTS(DEPARTMENTID) ON DELETE SET NULL @

-- Foreign keys for table 'TESTFOREIGNKEYOPTIONS4'
ALTER TABLE TESTFOREIGNKEYOPTIONS4
  ADD CONSTRAINT FK_TESTFOREIGNKEYOPTIONS4_DEPARTMENTID
  FOREIGN KEY (DEPARTMENTID)
  REFERENCES DEPARTMENTS(DEPARTMENTID) ON DELETE RESTRICT @

